# -*- coding:utf-8 -*-
import sqlite3
"""
在爬虫文件的pipelines中设置SqlitePipeline
'ITEM_PIPELINES': {
    'SpiderPath.pipelines.SqlitePipeline': 300,
    
}


SqlitePipeline管道类：
0、连接数据库，在custom_settings中配置 DB_NAME，没有配置则默认为db
1、以item的类名创建表名
2、动态创建建表语句：
    常用的几种数据类型：
        1、整型
        2、浮点型
        3、字符串类型
3、如果item中有含有url的字段，则把这个字段设置成 UNIQUE
4、动态生成插入语句
"""


# SqlitePipeline
class SqlitePipeline(object):
    def __init__(self, db_name):
        if not db_name:
            db_name = "db"
        self.db = sqlite3.connect(f"{db_name}.sqlite")
        self.cursor = self.db.cursor()

    @classmethod
    def from_settings(cls, settings):
        db_name = settings['DB_NAME']
        return cls(db_name)

    def close_spider(self, spider):
        self.cursor.close()
        self.cursor = None
        self.db.close()
        self.db = None

    def process_item(self, item, spider):
        create_table_last_part = ""
        lie_list = []
        value_list = []
        for key, value in item.items():
            if isinstance(value, int):
                value_type = "INTEGER"
            elif isinstance(value, str):
                value_type = "VARCHAR(255)"
            elif isinstance(value, float):
                value_type = "FLOAT"
            elif isinstance(value, list):
                value_type = "VARCHAR(255)"
                value = value[0]
            if "url" in key:
                value_type += " UNIQUE"
            create_table_last_part += f"{key} {value_type},"
            lie_list.append(key)
            value_list.append(value)
        sql1 = f"""
                    CREATE TABLE IF NOT EXISTS {type(item).__name__} (
                        id INTEGER PRIMARY KEY  AUTOINCREMENT,
                        {create_table_last_part[:-1]}
                    )
                """
        self.cursor.execute(sql1)
        try:
            sql1 = f"""INSERT INTO {type(item).__name__} ({",".join(lie_list)}) VALUES ({("?,"*len(lie_list))[:-1]})"""
            self.cursor.execute(sql1, value_list)
            self.db.commit()
        except Exception as e:
            print("插入失败！数据重复", e)
        return item
